#!/bin/bash

APP=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

common_sql="use ${APP};set hive.exec.dynamic.partition.mode=nonstrict;"

dim_user_zip_sql="
    with effective_record as (
        select
            id,
            login_name,
            nick_name,
            name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date
        from dim_user_zip
        where end_date = '9999-12-31'
    ), today_record as (
        select
            *
        from (
            select
                data.id,
                data.login_name,
                data.nick_name,
                md5(data.name) name,
                md5(if(data.phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\\d{8}$',
                    data.phone_num,
                    null)
                ) phone_num,
                md5(if(data.email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\\.[a-zA-Z0-9_-]+)+$',
                    data.email,
                    null)
                ) email,
                data.user_level,
                data.birthday,
                data.gender,
                data.create_time,
                data.operate_time,
                '${do_date}' start_date,
                '9999-12-31' end_date,
                row_number() over (partition by data.id order by data.create_time) rk
            from ods_user_info_inc
            where dt = '${do_date}') t1
        where t1.nick_name is not null
    ) , full_record as (
        select
            *
        from effective_record
        union
        select
            id,
            login_name,
            nick_name,
            name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date
        from today_record
    )
    insert overwrite table dim_user_zip partition (dt)
    select
        id,
        login_name,
        nick_name,
        name,
        phone_num,
        email,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        start_date,
        if (rk == 1, '9999-12-31', cast(date_sub('${do_date}', 1) as string)) end_date,
        if (rk == 1, '9999-12-31', cast(date_sub('${do_date}', 1) as string)) dt
    from (
        select
            id,
            login_name,
            nick_name,
            name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            row_number() over (partition by id order by start_date desc) rk
        from full_record
    ) t;
"

dim_sku_full_sql="
    with sku as (
        select
            id,
            spu_id,
            price,
            sku_name,
            sku_desc,
            weight,
            tm_id,
            category3_id,
            is_sale,
            create_time
        from ods_sku_info_full
    ), spu as (
        select
            id,
            spu_name
        from ods_spu_info_full
        where dt='${do_date}'
    ), brand as (
        select
            id,
            tm_name
        from ods_base_trademark_full
        where dt='${do_date}'
    ), category3 as (
        select
            id,
            name category3_name,
            category2_id
        from ods_base_category3_full
        where dt='${do_date}'
    ), category2 as (
        select
            id,
            name category2_name,
            category1_id
        from ods_base_category2_full
        where dt='${do_date}'
    ), category1 as (
        select
            id,
            name category1_name
        from ods_base_category1_full
        where dt='${do_date}'
    ), ssav as (
        select
            sku_id,
            collect_list(named_struct('sale_attr_id', sale_attr_id, 'sale_attr_name', sale_attr_name, 'sale_attr_value_id', sale_attr_value_id, 'sale_attr_value_name', sale_attr_value_name)) sku_sale_attr_values
        from ods_sku_sale_attr_value_full
        where dt='${do_date}'
        group by sku_id
    ), sav as (
        select
            sku_id,
            collect_list(named_struct('attr_id', attr_id, 'attr_name', attr_name, 'value_id', value_id, 'value_name', value_name)) sku_attr_values
        from ods_sku_attr_value_full
        where dt='${do_date}'
        group by sku_id
    )
    insert overwrite table dim_sku_full partition(dt='${do_date}')
    select
        sku.id,
        price,
        sku_name,
        sku_desc,
        weight,
        is_sale,
        spu_id,
        spu_name,
        tm_id,
        tm_name,
        category3_id,
        category3_name,
        category2_id,
        category2_name,
        category1_id,
        category1_name,
        sku_sale_attr_values,
        sku_attr_values,
        create_time
    from sku
    left join spu on sku.spu_id = spu.id
    left join brand on sku.tm_id = brand.id
    left join category3 on sku.category3_id = category3.id
    left join category2 on category3.category2_id = category2.id
    left join category1 on category2.category1_id = category1.id
    left join ssav on sku.id = ssav.sku_id
    left join sav on sku.id = sav.sku_id;
"

dim_province_full_sql="
    with province as (
        select
            id,
            name province_name,
            region_id,
            area_code,
            iso_code,
            iso_3166_2
        from ods_base_province_full
        where dt = '${do_date}'
    ), region as (
        select
            id,
            region_name
        from ods_base_region_full
        where dt = '${do_date}'
    )
    insert overwrite table dim_province_full partition (dt = '${do_date}')
    select
        province.id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2,
        region_id,
        region_name
    from province
    left join region on province.region_id = region.id;
"

dim_coupon_full_sql="
    with coupon as (
        select
            id,
            coupon_name,
            coupon_type coupon_type_code,
            condition_amount,
            condition_num,
            activity_id,
            benefit_amount,
            benefit_discount,
            create_time,
            range_type range_type_code,
            limit_num,
            taken_count,
            start_time,
            end_time,
            operate_time,
            expire_time
        from ods_coupon_info_full
        where dt = '${do_date}'
    ), coupon_type as (
        select
            dic_code,
            dic_name coupon_type_name
        from ods_base_dic_full
        where parent_code = '32' and dt = '${do_date}'
    ), range_type as (
        select
            dic_code,
            dic_name range_type_name
        from ods_base_dic_full
        where parent_code = '33' and dt = '${do_date}'
    )
    insert overwrite table dim_coupon_full partition (dt = '${do_date}')
    select
        id,
        coupon_name,
        coupon_type_code,
        coupon_type_name,
        condition_amount,
        condition_num,
        activity_id,
        benefit_amount,
        benefit_discount,
        case coupon_type_code
            when '3201' then concat('满', condition_amount, '元减', benefit_amount, '元')
            when '3202' then concat('满', condition_num, '件打', 10 * (1 - benefit_discount), '折')
            when '3203' then concat('减', benefit_amount, '元')
        end benefit_rule,
        create_time,
        range_type_code,
        range_type_name,
        limit_num,
        taken_count,
        start_time,
        end_time,
        operate_time,
        expire_time
    from coupon
    left join coupon_type on coupon.coupon_type_code = coupon_type.dic_code
    left join range_type on coupon.range_type_code = range_type.dic_code;
"

dim_activity_full_sql="
    with activity_rule as (
        select
            id activity_rule_id,
            activity_id,
            activity_type activity_type_code,
            condition_amount,
            condition_num,
            benefit_amount,
            benefit_discount,
            case activity_type
                when '3101' then concat('满',condition_amount,'元减',benefit_amount,'元')
                when '3102' then concat('满',condition_num,'件打',10*(1-benefit_discount),'折')
                when '3103' then concat('打',10*(1-benefit_discount),'折')
                end benefit_rule,
            benefit_level
        from ods_activity_rule_full
        where dt = '${do_date}'
    ), activity as (
        select
            id,
            activity_name,
            activity_desc,
            start_time,
            end_time,
            create_time
        from ods_activity_info_full
        where dt = '${do_date}'
    ), activity_type as (
        select
            dic_code,
            dic_name activity_type_name
        from ods_base_dic_full
        where dt = '${do_date}' and parent_code = '31'
    )
    insert overwrite table dim_activity_full partition (dt = '${do_date}')
    select
        activity_rule_id,
        activity_id,
        activity_name,
        activity_type_code,
        activity_type_name,
        activity_desc,
        start_time,
        end_time,
        create_time,
        condition_amount,
        condition_num,
        benefit_amount,
        benefit_discount,
        benefit_rule,
        benefit_level
    from activity_rule
    left join activity on activity_rule.activity_id = activity.id
    left join activity_type on activity_type.dic_code = activity_rule.activity_type_code;
"

# 判断表名参数，表名参数是第一个参数
case $1 in
"dim_user_zip")
    hive -e "${common_sql}${dim_user_zip_sql}"
;;
"dim_sku_full")
    hive -e "${common_sql}${dim_sku_full_sql}"
;;
"dim_province_full")
    hive -e "${common_sql}${dim_province_full_sql}"
;;
"dim_coupon_full")
    hive -e "${common_sql}${dim_coupon_full_sql}"
;;
"dim_activity_full")
    hive -e "${common_sql}${dim_activity_full_sql}"
;;
"all")
    hive -e "${common_sql}${dim_user_zip_sql}${dim_sku_full_sql}${dim_province_full_sql}${dim_coupon_full_sql}${dim_activity_full_sql}"
;;
esac